//
//  ClientDBO.m
//  POS
//
//  Created by sigma xu on 5/6/13.
//  Copyright (c) 2013 sigma xu. All rights reserved.
//

#import "ClientDBO.h"

@implementation ClientDBO

@synthesize id_client,client_code,client_name,contact,tel,fax,address,status;
-(id)init{
    if(self = [super init]){
        
    }
    return self;
}
-(void)open_db{
    NSString* docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString* dbPath = [docPath stringByAppendingPathComponent:@"pos.sqlite"];
    
    if (sqlite3_open([dbPath UTF8String], &posDB) != SQLITE_OK) {
        NSLog(@"Failed to open database!");
    }
}
-(NSMutableArray *) load_clients_list{
    [self open_db];
    client_list = [[NSMutableArray alloc] init];
    NSString *query = [NSString stringWithFormat:@"SELECT * from client where status>0 "];
    if(sqlite3_prepare(posDB, [query UTF8String], -1, &selectstmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement1 : '%s'",sqlite3_errmsg(posDB));
    }else{
        while (sqlite3_step(selectstmt) == SQLITE_ROW) {
            
            ClientDBO *cdbo = [[ClientDBO alloc] init];
            cdbo.id_client = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 0)];
            cdbo.client_code = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 1)];
            cdbo.client_name = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 2)];
            cdbo.contact = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 3)];
            cdbo.tel = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 4)];
            cdbo.fax = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 5)];
            cdbo.address = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 6)];
            cdbo.status = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 7)];
            [client_list addObject:cdbo];
        }
        sqlite3_finalize(selectstmt);
    }
    return client_list;
}
-(NSMutableArray *) load_favorites_list{
    [self open_db];
    client_list = [[NSMutableArray alloc] init];
    NSString *query = [NSString stringWithFormat:@"SELECT * from client where status=2 "];
    if(sqlite3_prepare(posDB, [query UTF8String], -1, &selectstmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement1 : '%s'",sqlite3_errmsg(posDB));
    }else{
        while (sqlite3_step(selectstmt) == SQLITE_ROW) {
            ClientDBO *cdbo = [[ClientDBO alloc] init];
            cdbo.id_client = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 0)];
            cdbo.client_code = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 1)];
            cdbo.client_name = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 2)];
            cdbo.contact = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 3)];
            cdbo.tel = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 4)];
            cdbo.fax = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 5)];
            cdbo.address = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 6)];
            cdbo.status = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 7)];
            [client_list addObject:cdbo];
        }
        sqlite3_finalize(selectstmt);
    }
    sqlite3_close(posDB);
    return client_list;
}
-(void) add_client_to_favorite:(NSString *)client_id{
    [self open_db];
    NSString *query = [NSString stringWithFormat:@"update client Set status = 2 Where id_client = '%@'",client_id];
    const char *sql = [query UTF8String];
    if(sqlite3_prepare(posDB, sql, -1, &updatestmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
//        while (sqlite3_step(updatestmt) == SQLITE_ROW) {
//        }
//        
        if(SQLITE_DONE != sqlite3_step(updatestmt)){
            NSLog(@"Problem with prepare statement 2: '%s'",sqlite3_errmsg(posDB));
        }
        sqlite3_reset(updatestmt);
    }
    sqlite3_close(posDB);
}
-(void) remove_favorite:(NSString *)client_id{
    [self open_db];
    NSString *query = [NSString stringWithFormat:@"update client Set status = 1 Where id_client = '%@'",client_id];
    const char *sql = [query UTF8String];
    if(sqlite3_prepare(posDB, sql, -1, &updatestmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        while (sqlite3_step(updatestmt) == SQLITE_ROW) {
        }
        sqlite3_reset(updatestmt);
    }
    sqlite3_close(posDB);
}
-(ClientDBO *) get_client_by_id:(NSString *)client_id{
    [self open_db];
    ClientDBO *cdbo = [[ClientDBO alloc] init];
    NSString *query = [NSString stringWithFormat:@"SELECT * from client where status>0 and id_client='%@'",client_id];
    if(sqlite3_prepare(posDB, [query UTF8String], -1, &selectstmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement1 : '%s'",sqlite3_errmsg(posDB));
    }else{
        if(sqlite3_step(selectstmt) == SQLITE_ROW) {
            cdbo.id_client = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 0)];
            cdbo.client_code = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 1)];
            cdbo.client_name = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 2)];
            cdbo.contact = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 3)];
            cdbo.tel = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 4)];
            cdbo.fax = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 5)];
            cdbo.address = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 6)];
            cdbo.status = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(selectstmt, 7)];
            [client_list addObject:cdbo];
        }
        sqlite3_finalize(selectstmt);
    }
    sqlite3_close(posDB);
    return cdbo;
}
-(void)insert_client{
    [self open_db];
    const char *sql = "insert into client (client_code, client_name, contact, tel, fax, address,status,remark) values (?,?,?,?,?,?,?,?)";
    if(sqlite3_prepare(posDB, sql, -1, &insertstmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        sqlite3_bind_text(insertstmt, 1, [client_code UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(insertstmt, 2, [client_name UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(insertstmt, 3, [contact  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(insertstmt, 4, [tel  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(insertstmt, 5, [fax  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(insertstmt, 6, [address  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(insertstmt, 7, [status  UTF8String], -1, SQLITE_TRANSIENT);
        if(SQLITE_DONE != sqlite3_step(insertstmt))
            NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
        else{
            sqlite3_last_insert_rowid(posDB);
        }
        sqlite3_reset(insertstmt);
    }
    sqlite3_close(posDB);
}
-(void)clear_client{
    [self open_db];
    const char *dropsql = "drop table client";
    if(sqlite3_exec(posDB, dropsql, NULL, NULL, NULL) == SQLITE_OK){
        NSLog(@"Table Delete");
    }
    const char *createsql = "CREATE TABLE client (id_client INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL, client_code VARCHAR, client_name VARCHAR, contact VARCHAR, tel VARCHAR, fax VARCHAR, address VARCHAR, status INTEGER DEFAULT 1, remark VARCHAR)";
    if(sqlite3_exec(posDB, createsql, NULL, NULL, NULL) == SQLITE_OK){
        NSLog(@"Table Create");
    }
    sqlite3_close(posDB);
}
-(void)finalize{
    
    [super finalize];
}

@end
